package pi.servlets.com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

public class Conexion {
	Connection con;
    Statement st;
    ResultSet rs;
    Usuario usuario;
    Vehiculo coche;
    int idUser;
    String nombre;
    Request peticion;
    Points punto;
    Route ruta;
    Consulta consulta;
    ArrayList<Resultados> resultado;
  
    Conexion ( Usuario usu) {
    	usuario = usu;
  	  	con     = null;
        st      = null;
        rs      = null;
    }
    Conexion ( Vehiculo vehiculo) {
    	coche   = vehiculo;
  	  	con     = null;
        st      = null;
        rs      = null;
    }
    Conexion ( Request request) {
    	peticion   = request;
  	  	con        = null;
        st         = null;
        rs         = null;
    }
    Conexion ( Route route) {
    	ruta       = route;
  	  	con        = null;
        st         = null;
        rs         = null;
    }
    Conexion ( Points punt) {
    	punto      = punt;
  	  	con        = null;
        st         = null;
        rs         = null;
    }
    Conexion ( Consulta cons) {
    	consulta = cons;
  	  	con      = null;
        st       = null;
        rs       = null;
    }
    
    Conexion (ArrayList<Resultados> res) {
    	resultado = res;
  	  	con      = null;
        st       = null;
        rs       = null;
    }
    
    public void conectar (){
        try {
        	Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/uvipool","root", "root");
            if(!con.isClosed()){
                System.out.println("Conectado con exito a la base de datos.");
              
            }    
        } 
        catch(Exception e) {
        	System.err.println("Excepcion: "+e.getMessage());
        	} 
    }
    public int insertar2 (Usuario usu) throws SQLException{
    	
    	
    	String name           = usu.getNombre();
    	String surname        = usu.getSurname();
    	String nick           = usu.getNick();
    	String password       = usu.getPassword();
    	String secretQuestion = usu.getSecretQuestion();
    	String secretAnswer   = usu.getSecretAnswer();
    	String address		  = usu.getAddress();
    	String city  		  = usu.getCity();
    	String state		  = usu.getState();
    	String country		  = usu.getCountry();
    	String email          = usu.getEmail();
    	String telephone	  = usu.getTelephone();
    	String sipAddress	  = usu.getSipAddress();
    	String zipCode	  	  = usu.getZipCode();
    
    	PreparedStatement stmt=con.prepareStatement("INSERT INTO users(name,surname,nick,password,secretQuestion,secretAnswer,address,city,state,country,email,telephone,sipAddress,zipCode) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		//PreparedStatement stmt=con.prepareStatement("INSERT INTO Users(name,surname,nick,password,secretQuestion,secretAnswer,address,city,state,country,email,telephone,sipAddress,zipCode) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    	stmt.setString(1,name);
    	stmt.setString(2,surname);
    	stmt.setString(3,nick);
    	stmt.setString(4,password);
    	stmt.setString(5,secretQuestion);
    	stmt.setString(6,secretAnswer);
    	stmt.setString(7,address);
    	stmt.setString(8,city);
    	stmt.setString(9,state);
    	stmt.setString(10,country);
    	stmt.setString(11,email);
    	stmt.setString(12,telephone);
    	stmt.setString(13,sipAddress);
    	stmt.setString(14,zipCode);
    	int retorno= stmt.executeUpdate();
    	System.out.println(retorno);
    	stmt.close();
    	
    	Statement st = con.createStatement();
    	ResultSet rs = st.executeQuery("SELECT * FROM users");
    	rs.last();
    	int id=rs.getInt("idUser");
    	System.out.println("El idUSer es-----> " + id);
    	rs.close();
    	return id;
    }
    public boolean comprobarUsuario (Usuario usu) throws SQLException{
    	boolean encontrado;
    	String nick = usu.getNick();
    	st          = con.createStatement();
    	rs          = st.executeQuery("SELECT * FROM users");
        rs.beforeFirst();
        while(rs.next()) {
            String alias= rs.getString("nick");
            if (alias!= null){
	            if (alias.equals(nick)){
	            	idUser=rs.getInt("idUser");
	            	encontrado=true;
	            	return encontrado;
	            }
            }
        }
        encontrado=false;
    	return encontrado;
    }
    public boolean comprobarUsuario2 (Usuario usu) throws SQLException{
    	boolean encontrado;
    	String nick     = usu.getNick();
    	String password = usu.getPassword();
    	st          = con.createStatement();
    	rs          = st.executeQuery("SELECT * FROM users");
        rs.beforeFirst();
        while(rs.next()) {
            String alias = rs.getString("nick");
            if (alias!= null){
	            if (alias.equals(nick)){
	            	String pass = rs.getString("password");
	            	if (pass.equals(password)){
		            	idUser=rs.getInt("idUser");
		            	nombre=rs.getString("name");
		            	encontrado=true;
		            	return encontrado;
	            	}
	            	else{
	            		encontrado=false;
	            		return encontrado;
	            	}
	            }
            }
        }
        encontrado=false;
    	return encontrado;
    }
    public boolean comprobarVehiculo (Vehiculo vehiculo) throws SQLException{
    	boolean encontrado;
    	String plate = vehiculo.getPlate();
    	st          = con.createStatement();
    	rs          = st.executeQuery("SELECT * FROM Vehicles");
        rs.beforeFirst();
        while(rs.next()) {
            String matricula= rs.getString("plate");
            if (matricula!= null){
	            if (matricula.equals(plate)){
	            	encontrado=true;
	            	return encontrado;
	            }
            }
        }
        encontrado=false;
    	return encontrado;
    }
    public int insertarVehiculo (Vehiculo vehiculo) throws SQLException{
    	
    	
    	
		 String  plate = vehiculo.getPlate();
		 String  brand = vehiculo.getBrand();
		 String  model = vehiculo.getModel();
		 String  color = vehiculo.getColor();
		 int     idUser= vehiculo.getIdUser();
	    	
		PreparedStatement stmt=con.prepareStatement("INSERT INTO vehicles(idUser,plate,brand,model,color) VALUES (?,?,?,?,?)");
		stmt.setInt(1,idUser);
		stmt.setString(2,plate);
		stmt.setString(3,brand);
		stmt.setString(4,model);
		stmt.setString(5,color);
		
		int retorno= stmt.executeUpdate();
		System.out.println(retorno);
		stmt.close();
		return retorno;
    }
    
    public int insertarRequest (Request peticion) throws SQLException, ParseException{
		 String  fecha       = peticion.getFecha();
		 fecha               = fecha+"-";
		 String  hora        = peticion.getHora();
		 String  maxCost     = peticion.getMaxCost();
		 String  timeMargin  = peticion.getTimeMargin();
		 String  status      = peticion.getStatus();
		 String  seats       = peticion.getSeats();
		 int     idUser 	 = peticion.getIdUser();
		 
		 
		 
		 int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		 Calendar data=Utils.pasarACalendar(vector);
		 Date datetime=data.getTime();
		 System.out.println("La fecha es --> " +datetime );
		 
		 PreparedStatement stmt=con.prepareStatement("INSERT INTO request(idUser,seats,date,timeMargin,maxCost,status) VALUES (?,?,?,?,?,?)");
		 stmt.setInt(1,idUser);
		 stmt.setString(2,seats);
		 stmt.setTimestamp(3, new Timestamp(datetime.getTime()));
		 stmt.setString(4,timeMargin);
		 stmt.setString(5,maxCost);
		 stmt.setString(6,status);
		 
		 
		 int retorno= stmt.executeUpdate();
		 System.out.println(retorno);
		 stmt.close();
		 Statement st = con.createStatement();
		 ResultSet rs = st.executeQuery("SELECT * FROM request");
		 rs.last();
		 int id=rs.getInt("idRequest");
		 System.out.println("El idRequest es-----> " + id);
		 rs.close();
		 return id;
		 
  }
    public int insertarRuta(Route ruta) throws SQLException, ParseException{
		 String  fecha              = ruta.getFecha();
		 fecha                      = fecha+"-";
		 String  hora               = ruta.getHora();
		 String  peajes             = ruta.getAvoid();
		 String  seatsOffered       = ruta.getSeatsOffered();
		 String  seatsAvailable     = ruta.getSeatsAvailable();
		 String  maxDeviation       = ruta.getMaxDeviation();
		 String  remainingDeviation = ruta.getRemainingDeviation();
		 String  maxDelay           = ruta.getMaxDelay();
		 String  remainingDelay     = ruta.getRemainingDelay();
		 String  fumador            = ruta.getSmoking();
		 String  costModel          = ruta.getCostModel();
		 String  costAmount         = ruta.getCostAmount();
		 int     idUser 	        = ruta.getIdUser();
		 
		 int avoid=2,smoking=2;
		 
		 if ("NO".equals(peajes)){
			 avoid=0;
		 }
		 else if("SI".equals(peajes)){
			 avoid=1;
		 }
		 
		 if ("NO".equals(fumador)){
			 smoking=0;
		 }
		 else if("SI".equals(fumador)){
			 smoking=1;
		 }
		 
		 int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		 Calendar data=Utils.pasarACalendar(vector);
		 Date datetime=data.getTime();
		 System.out.println("La fecha es --> " +datetime );
		 
		 PreparedStatement stmt=con.prepareStatement("INSERT INTO routes(idUser,avoid,seatsOffered,seatsAvailable,maxDeviation,remainingDeviation,maxDelay,remainingDelay,date,smoking,costModel,costAmount) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
		 stmt.setInt(1,idUser);
		 stmt.setInt(2,avoid);
		 stmt.setString(3,seatsOffered);
		 stmt.setString(4,seatsAvailable);
		 stmt.setString(5,maxDeviation);
		 stmt.setString(6,remainingDeviation);
		 stmt.setString(7,maxDelay);
		 stmt.setString(8,remainingDelay);
		 stmt.setTimestamp(9, new Timestamp(datetime.getTime()));
		 stmt.setInt(10,smoking);
		 stmt.setString(11,costModel);
		 stmt.setString(12,costAmount);
		 
		
		 int retorno= stmt.executeUpdate();
		 System.out.println(retorno);
		 stmt.close();
		 Statement st = con.createStatement();
		 ResultSet rs = st.executeQuery("SELECT * FROM routes");
		 rs.last();
		 int id=rs.getInt("idRoute");
		 System.out.println("El idRoute es-----> " + id);
		 rs.close();
		 return id;		 
 }
    
    public int insertarPunto (Points punto) throws SQLException, ParseException{
    	
    	
    	int idRoute     = punto.idRoute;
    	int idRequest   = punto.idRequest;
    	String address  = punto.address;
    	float latitude  = punto.latitud;
    	float longitude = punto.longitud;
    	int position    = punto.position;
    	int type        = punto.type;
    	
		PreparedStatement stmt=con.prepareStatement("INSERT INTO points(idRoute,idRequest,address,latitude,longitude,position,type) VALUES (?,?,?,?,?,?,?)");
		stmt.setInt(1,idRoute);
		stmt.setInt(2,idRequest);
		stmt.setString(3, address);
		stmt.setFloat(4,latitude);
		stmt.setFloat(5,longitude);
		stmt.setInt(6,position);
		stmt.setInt(7,type);
		 
		int retorno= stmt.executeUpdate();
		System.out.println(retorno);
		stmt.close();
		return retorno; 
 }
    
    
    
   
	public ArrayList<Resultados> buscarRuta (Consulta cons) throws SQLException{
    	
		  
		ArrayList<Resultados> resultado = new ArrayList<Resultados>();
		String tipoUsuario              = cons.getTipoUsuario();
    	String origen                   = cons.getOrigen();
    	String destino                  = cons.getDestino();
    	
    	String usuarioOrigen            = Utils.cortarDireccion(origen);
    	String usuarioDestino           = Utils.cortarDireccion(destino);
    	
    	
    	String cadSQLBuscaPasajero   = "select * from points where type!=0 and (( position=0 and address like ?) or (position=255 and address like  ?))";
    	String cadSQLBuscaConductor  = "select * from points where type=0 and (( position=0 and address like ?) or (position=255 and address like  ?))";
    	Resultados resul = new Resultados();
    	
    	if ("CONDUCTOR".equals(tipoUsuario)){	
    		PreparedStatement stmt = con.prepareStatement(cadSQLBuscaPasajero);
    		stmt.setString(1,"%"+usuarioOrigen);
    		stmt.setString(2,"%"+usuarioDestino);	
    		rs = stmt.executeQuery();
    		
    		while(rs.next()){
    			if (rs.getInt("position")==0){
    				System.out.println("origen: "+rs.getString("address"));
					resul.setOrigen(rs.getString("address"));
					resul.setIdRequest(rs.getInt("idRequest"));
//    				resultado.add(resul);	
    						
    			}
    			else if (rs.getInt("position")==255){
    				if (resul.getIdRequest()==rs.getInt("idRequest")){
    					resul.setDestino(rs.getString("address"));
    					resultado.add(resul);
    					resul = new Resultados();
						
    				}
    			}	
    		}
    	}
    	else if ("PASAJERO".equals(tipoUsuario)){
    		PreparedStatement stmt=con.prepareStatement(cadSQLBuscaConductor);
    		stmt.setString(1,"%"+usuarioOrigen);
    		stmt.setString(2,"%"+usuarioDestino);		
    		rs = stmt.executeQuery();
    		while(rs.next()){
    			if (rs.getInt("position")==0){
    				System.out.println("origen: "+rs.getString("address"));
					resul.setOrigen(rs.getString("address"));
					resul.setIdRoute(rs.getInt("idRoute"));
    			}
    			else if (rs.getInt("position")==255){
    				if (resul.getIdRoute()==rs.getInt("idRoute")){
    					System.out.println("destino: "+rs.getString("address"));
    					resul.setDestino(rs.getString("address"));
    					resul.setIdRoute(rs.getInt("idRoute"));
						resultado.add(resul);
						resul = new Resultados();
    				}
    			}
    		} 		
    	}
    	return resultado;
    }
    
	
	
	public ArrayList<Resultados> buscarIdRequest (ArrayList<Resultados> resultado,float coste,String fech, String hor, String seatsO) throws SQLException, ParseException{
		
		// "select * from request where idRequest=? and maxCost<=? and (date>=? or date<=?)"
		float precioMaximo           = coste;
		String cadSQL                = "select * from request where idRequest=? and maxCost<=? and seats<=? and date between ? and ?";
		String hora                  = hor;
		String  fecha                = fech;
		fecha                        = fecha+"-";
		int seatsOffered             = Integer.parseInt(seatsO); 
		
		
		int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		Calendar data=Utils.pasarACalendar(vector);
		Date datetime=data.getTime(); 
		data.add(Calendar.HOUR, -2);
		Date datetime2 =data.getTime();
		
		System.out.println("La fecha1 es --> " +datetime);
		System.out.println("La fecha2 es --> " +datetime2);
		int j=0;
		for (int i=0;i<resultado.size();i++){
			PreparedStatement stmt = con.prepareStatement(cadSQL);
			stmt.setInt(1, resultado.get(i).getIdRequest());
			stmt.setFloat(2,precioMaximo);
			stmt.setInt(3, seatsOffered);
			stmt.setTimestamp(4, new Timestamp(datetime2.getTime()));
			stmt.setTimestamp(5, new Timestamp(datetime.getTime()));
			rs = stmt.executeQuery();
			while(rs.next()){
				usuario = new Usuario();
				usuario.setIdUser(rs.getInt("idUser"));
				resultado.get(j).setUsu(usuario);
				resultado.get(j).setStatus(rs.getInt("status"));
				resultado.get(j).setSeatsAvailable(rs.getInt("seats"));
				resultado.get(j).setTimeMargin(rs.getInt("timeMargin"));
				resultado.get(j).setMaxCost(rs.getInt("maxCost"));
				j++;
			}
		}
		return resultado;
	}
public ArrayList<Resultados> buscarIdRoute (ArrayList<Resultados> resultado,float coste,String fech, String hor,String fumador, String peajes, String seatsO) throws SQLException, ParseException{
		
		// "select * from request where idRequest=? and maxCost<=? and (date>=? or date<=?)"
		float precioMaximo           = coste;
		int smoking=-1;
		int avoid=-1;
		int seatsOffered=-1;
		String cadSQL                = "select * from routes where idRoute=? and costAmount<=? and smoking=? and avoid=? and seatsAvailable>=? and date between ? and ?";
		String hora                  = hor;
		String  fecha                = fech;
		fecha                        = fecha+"-";
		
		smoking=Utils.usuarioFumador(fumador);
		avoid=Utils.evitaPeajes(peajes);
		seatsOffered= Integer.parseInt(seatsO);
		//COMPROBAR
		int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		Calendar data=Utils.pasarACalendar(vector);
		Date datetime=data.getTime(); 
		data.add(Calendar.HOUR, -2);
		Date datetime2 =data.getTime(); 
		
		System.out.println("La fecha1 es --> " +datetime);
		System.out.println("La fecha2 es --> " +datetime2);
		int j=0;
		for (int i=0;i<resultado.size();i++){
			PreparedStatement stmt = con.prepareStatement(cadSQL);
			stmt.setInt(1, resultado.get(i).getIdRoute());
			stmt.setFloat(2,precioMaximo);
			stmt.setInt(3, smoking);
			stmt.setInt(4, avoid);
			stmt.setInt(5, seatsOffered);
			stmt.setTimestamp(6, new Timestamp(datetime2.getTime()));
			stmt.setTimestamp(7, new Timestamp(datetime.getTime()));
			rs = stmt.executeQuery();
			while(rs.next()){
				Usuario usu = new Usuario();
				usu.setIdUser(rs.getInt("idUser"));
				resultado.get(j).setUsu(usu);
				resultado.get(j).setSeatsAvailable(rs.getInt("seatsAvailable"));
				resultado.get(j).setSeatsOffered(seatsOffered);
				resultado.get(j).setTimeMargin(rs.getInt("maxDelay"));
				resultado.get(j).setCostAmount(rs.getFloat("costAmount"));
				int a = rs.getInt("avoid");
				int b = rs.getInt("smoking");
				resultado.get(j).setAvoid(Utils.peajesRespuestaBBDD(a));
				resultado.get(j).setSmoking(Utils.fumadorRespuestaBBDD(b));
				j++;
			}
		}
		return resultado;
	}
	public ArrayList<Resultados> buscarVehiculo (ArrayList<Resultados> resultado) throws SQLException{
		
		
		String cadSQL       = "select * from vehicles where idUser=?";
		int j=0;
		for(int i=0;i<resultado.size();i++){
			PreparedStatement stmt = con.prepareStatement(cadSQL);
			stmt.setInt(1, resultado.get(i).getUsu().getIdUser());
			rs = stmt.executeQuery();
			while(rs.next()){
				Vehiculo auto = new Vehiculo();
				auto.setIdUser(rs.getInt("idUser"));
				auto.setPlate(rs.getString("plate"));
				auto.setBrand(rs.getString("brand"));
				auto.setModel(rs.getString("model"));
				auto.setColor(rs.getString("color"));
				resultado.get(j).setCoche(auto);
				j++;
			}
		}	
		return resultado;
	}
	
	public ArrayList<Resultados> buscarUsuario (ArrayList<Resultados> resultado) throws SQLException{
		
		
		String cadSQL       = "select * from users where idUser=?";
		int j=0;
		for(int i=0;i<resultado.size();i++){
			PreparedStatement stmt = con.prepareStatement(cadSQL);
			stmt.setInt(1, resultado.get(i).getUsu().getIdUser());
			rs = stmt.executeQuery();
			while(rs.next()){
				Usuario usu=new Usuario(resultado.get(i).getUsu().getIdUser());
				usu.setNick(rs.getString("nick"));
				usu.setEmail(rs.getString("email"));
				resultado.get(j).setUsu(usu);
				j++;
			}
		}
		return resultado;
	}
	
	
public ArrayList<Resultados>  insertarAgreement (ArrayList<Resultados> res) throws SQLException, ParseException{
    	
	
    	
    	int idRoute     = res.get(0).getIdRoute();
    	int idRequest   = res.get(0).getIdRequest();
    	String fecha    = res.get(0).getFecha();
    	fecha           = fecha+"-";
    	String hora     = res.get(0).getHora();
    	int status      = 0;
    	
    	int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		Calendar data=Utils.pasarACalendar(vector);
		Date datetime=data.getTime(); 
    	
    	
    	
		PreparedStatement stmt=con.prepareStatement("INSERT INTO agreements(idRoute,idRequest,date,status) VALUES (?,?,?,?)");
		stmt.setInt(1,idRoute);
		stmt.setInt(2,idRequest);
		stmt.setTimestamp(3, new Timestamp(datetime.getTime()));
		stmt.setInt(4,status);
		 
		int retorno= stmt.executeUpdate();
		System.out.println(retorno);
		stmt.close();
		if (retorno==1);{
			res.get(0).setInsertado(true);
		}
		return res;
 }


	public ArrayList<Resultados> actualizarAsientosDisponibles (ArrayList<Resultados> resultado) throws SQLException{
		
		int seatsAvailable =-1;
		int seatsOffered   = -1;
		String cadSQL      = "update routes set seatsAvailable=? where idRoute=?";
		seatsOffered       = resultado.get(0).getSeatsOffered();
		seatsAvailable     = resultado.get(0).getSeatsAvailable()-seatsOffered;
		PreparedStatement stmt = con.prepareStatement(cadSQL);
		stmt.setInt(1,seatsAvailable);
		stmt.setInt(2, resultado.get(0).getIdRoute());
		int i = stmt.executeUpdate();
		System.out.println("filas afectadas--->"+i);
		resultado.get(0).setSeatsAvailable(seatsAvailable);
		return resultado;
	}
	
public ArrayList<Resultados> actualizarAsientosDisponiblesConductor (ArrayList<Resultados> resultado) throws SQLException{
		
		int seatsAvailable =-1;
		String fecha    = resultado.get(0).getFecha();
    	fecha           = fecha+"-";
    	String hora     = resultado.get(0).getHora();
    	int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		Calendar data=Utils.pasarACalendar(vector);
		Date datetime=data.getTime();
		data.add(Calendar.HOUR, -2);
		Date datetime2 =data.getTime();
		
		resultado=buscarAsientosTotalesConductor(resultado);

		String cadSQL      = "update routes set seatsAvailable=? where idUser=? and date between ? and ?";
		seatsAvailable     = resultado.get(0).getseatsOffered()-resultado.get(0).getSeatsAvailable();
		PreparedStatement stmt = con.prepareStatement(cadSQL);
		stmt.setInt(1,seatsAvailable);
		stmt.setInt(2, resultado.get(0).getIdUserConductor());
		stmt.setTimestamp(3, new Timestamp(datetime2.getTime()));
		stmt.setTimestamp(4, new Timestamp(datetime.getTime()));
		int i = stmt.executeUpdate();
		System.out.println("filas afectadas--->"+i);
		resultado.get(0).setSeatsAvailable(seatsAvailable);
		return resultado;
	}
	public ArrayList<Resultados> buscarAsientosTotalesConductor (ArrayList<Resultados> resultado) throws SQLException{
		
		
		String fecha    = resultado.get(0).getFecha();
		fecha           = fecha+"-";
		String hora     = resultado.get(0).getHora();
		int [] vector = Utils.pasarFechaParaCalendar(fecha+hora);
		Calendar data=Utils.pasarACalendar(vector);
		Date datetime=data.getTime();
		data.add(Calendar.HOUR, -2);
		Date datetime2 =data.getTime();
		
	
		String cadSQL      = "select * from routes where idUser=? and date between ? and ?";
		
		PreparedStatement stmt = con.prepareStatement(cadSQL);
		stmt.setInt(1, resultado.get(0).getIdUserConductor());
		stmt.setTimestamp(2, new Timestamp(datetime2.getTime()));
		stmt.setTimestamp(3, new Timestamp(datetime.getTime()));
		rs = stmt.executeQuery();
		while(rs.next()){
			resultado.get(0).setSeatsOffered(rs.getInt("seatsOffered"));
			resultado.get(0).setIdRoute(rs.getInt("idRoute"));
		}
		return resultado;
	}
	
	public int getIdUSer(){
		return idUser;
	}
	public String getNombre(){
		return nombre;
	}
}